﻿using System.Collections.Generic;
using System.Data.SQLite;
using StudentManagementSystem.Models;

namespace StudentManagementSystem.Data
{
    public class StudentRepository
    {
        private readonly string _connectionString;

        public StudentRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void Add(Student student)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "INSERT INTO Students (Name, Age, ClassId) VALUES (@Name, @Age, @ClassId); " +
                "SELECT last_insert_rowid();", connection);

            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@Age", student.Age);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);

            student.Id = (int)(long)command.ExecuteScalar();
        }

        public IEnumerable<Student> GetByClassId(int classId)
        {
            var students = new List<Student>();

            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "SELECT * FROM Students WHERE ClassId = @ClassId", connection);
            command.Parameters.AddWithValue("@ClassId", classId);

            using var reader = command.ExecuteReader();

            while (reader.Read())
            {
                students.Add(new Student
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Age = reader.GetInt32(2),
                    ClassId = reader.GetInt32(3)
                });
            }

            return students;
        }

        public Student GetById(int id)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("SELECT * FROM Students WHERE Id = @Id", connection);
            command.Parameters.AddWithValue("@Id", id);

            using var reader = command.ExecuteReader();

            if (reader.Read())
            {
                return new Student
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Age = reader.GetInt32(2),
                    ClassId = reader.GetInt32(3)
                };
            }

            return null;
        }

        public void Update(Student student)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "UPDATE Students SET Name = @Name, Age = @Age, ClassId = @ClassId WHERE Id = @Id", connection);

            command.Parameters.AddWithValue("@Id", student.Id);
            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@Age", student.Age);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);

            command.ExecuteNonQuery();
        }

        public void Delete(int id)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("DELETE FROM Students WHERE Id = @Id", connection);
            command.Parameters.AddWithValue("@Id", id);

            command.ExecuteNonQuery();
        }
    }
}